﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Web;
using System.Xml.Serialization;

namespace SuperFox.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class SuperService
    {

        private static Dictionary<string, USC> map = new Dictionary<string, USC>();

        [OperationContract]
        public void AddTempUser(DbUsers u)
        {

            map.Add(u.log_username, new USC() { U = u, SC = new ShoppingCartCollection() });

            //return PlaceOrder(tempU, tempSc);
        }

        [OperationContract]
        public void AddItemToShoppingTempShoppingCart(DbProducts pro, string un)
        {
            map[un].SC.Add(pro);
        }

        [OperationContract]
        public int DoOrder(string un)
        {
            return PlaceOrder(map[un].U, map[un].SC);
        }


        [OperationContract]
        public int DeleteUser(int uid)
        {

            try
            {

                DataClassesDataContext db = new DataClassesDataContext();
                DbUsers toDelete = db.DbUsers.Single(p => p.log_id == uid);
                db.DbUsers.DeleteOnSubmit(toDelete);
                db.SubmitChanges();
                return uid;

            }
            catch (Exception ex)
            {
                LogEception(ex, "DeleteUser");
                return -1;
            }
        }

        public void DecrementStock(ShoppingCartCollection cart)
        {
            string SQL = "";
            DataClassesDataContext db = new DataClassesDataContext();

            foreach (DbProducts p in cart)
            {
                foreach (DbProductOptionsValues v in p.Options.Values)
                {

                    SQL = "UPDATE shop_productoptionvalues SET pov_quantityavailable = '" + (int)(v.pov_quantityavailable - v.TotalSelected) + "' WHERE po_id = '" + v.po_id + "'";

                    using (SqlConnection cn = new SqlConnection(GetConnectionString()))
                    {
                        cn.Close();
                        cn.Open();

                        SqlCommand cmd = new SqlCommand(SQL, cn);

                        try
                        {
                            cmd.ExecuteNonQuery();

                        }
                        catch (Exception ex)
                        {
                            LogEception(ex, "DecrementStock");
                        }
                    }
                }
            }
        }

        [OperationContract]
        public List<int> GetNumberOfUsersForMerch(int merch_id)
        {
            DataClassesDataContext db = new DataClassesDataContext();
            return (from tempObj in db.DbUsers where tempObj.merc_id.Equals(merch_id) select tempObj.log_id).ToList<int>();
        }

        [OperationContract]
        public DbUsers GetSingleUsers(int user_id)
        {
            DataClassesDataContext db = new DataClassesDataContext();
            DbUsers user = db.DbUsers.Single(p => p.log_id.Equals(user_id));

            user.Programs = user.DbMarchPrograms;
            user.Programs.Products = user.Programs.DbProducts.ToList<DbProducts>();

            foreach (DbProducts pr in user.Programs.Products)
            {
                List<int> validIDs = Deserialize(pr.prod_users);

                // if something goes wrong just add the product
                if (validIDs == null)
                {
                    pr.Visible = true;
                    continue;
                }

                if (validIDs.Contains(user.log_id))
                {
                    pr.Visible = true;
                }
                else
                {
                    pr.Visible = false;
                }
            }

            return user;
        
        }

        [OperationContract]
        public List<DbUsers> GetAllUsers(int merc_id)
        {
            DataClassesDataContext db = new DataClassesDataContext();

            List<DbUsers> users = (from tempObj in db.DbUsers where tempObj.merc_id.Equals(merc_id) select tempObj).ToList<DbUsers>();


            foreach (DbUsers u in users)
            {
                //u.DbMarchPrograms.Products = u.DbMarchPrograms.DbProducts.ToList<DbProducts>();
                u.Programs = u.DbMarchPrograms;
                u.Programs.Products = u.Programs.DbProducts.ToList<DbProducts>();
                //u.DbMarchPrograms.Products = new List<DbProducts>();

                foreach (DbProducts pr in u.Programs.Products)
                {

                    List<int> validIDs = Deserialize(pr.prod_users);

                    // if something goes wrong just add the product
                    if (validIDs == null)
                    {
                        pr.Visible = true;
                        continue;
                    }

                    if (validIDs.Contains(u.log_id))
                    {
                        pr.Visible = true;
                    }
                    else
                    {
                        pr.Visible = false;


                    }


                }

                u.Programs = u.DbMarchPrograms;
            }

            return users;
        }

        private string Serialize(List<int> ids)
        {
            XmlSerializer serializer = new XmlSerializer(typeof(List<int>));

            StringWriter sw = new StringWriter();

            serializer.Serialize(sw, ids);

            return sw.ToString();

        }

        private void SaveProductVisibility(int loginID, int prodID, bool visibility, int merch_id)
        {
            DataClassesDataContext db = new DataClassesDataContext();
            DbProducts toUpdate = db.DbProducts.Single(p => p.prod_id == prodID);

            List<int> ids = Deserialize(toUpdate.prod_users);

            if (ids == null)
            {
                // this is a special case so new need to go and get all the users and add 
                ids = (from tempObj in db.DbUsers where tempObj.merc_id == merch_id select tempObj.log_id).ToList<int>();
            }


            // now that we have got a list of all the current ids we need to update is to include or remove the current one

            if (visibility)
            {
                // make sure its in the list
                if (!ids.Contains(loginID))
                {
                    ids.Add(loginID);
                }
                
            }
            else
            {
                // make sure its not in the list
                if (ids.Contains(loginID))
                {
                    ids.Remove(loginID);
                }
            }

            toUpdate.prod_users = Serialize(ids);
            db.SubmitChanges();



        }

        [OperationContract]
        private int SaveUser(DbUsers u)
        {
            
           




            // save the user details

            try
            {
                if (u.IsNew)
                {
                   
                    DataClassesDataContext db = new DataClassesDataContext();
                    DbUsers toAdd = u;
                    db.DbUsers.InsertOnSubmit(u);
                    db.SubmitChanges();



                    return u.log_id;

                }
                else
                {
                    DataClassesDataContext db = new DataClassesDataContext();
                    DbUsers toUpdate = db.DbUsers.Single(p => p.log_id == u.log_id);
                    toUpdate.log_password = MakeStringSafe(u.log_password);
                    toUpdate.log_username = MakeStringSafe(u.log_username);
                    toUpdate.log_caneditprogram = u.log_caneditprogram;
                    db.SubmitChanges();

                    // save the product visibilites

                    foreach (DbProducts p in u.Programs.Products)
                    {
                        SaveProductVisibility(u.log_id, p.prod_id,p.Visible, u.merc_id);

                    }


                    


                    return u.log_id;

                }

            }
            catch (Exception ex)
            {
                LogEception(ex, "SaveUser");
                return -1;
            }
          

        }

        [OperationContract]
        public bool SaveProgram(DbMarchPrograms prog)
        {


            try
            {
                DataClassesDataContext db = new DataClassesDataContext();
                DbMarchPrograms progToUpdate = db.DbMarchPrograms.Single(p => p.merc_id == prog.merc_id);
                progToUpdate.merc_deniedmessage = prog.merc_deniedmessage;
                progToUpdate.merc_confirmedmessage = prog.merc_confirmedmessage;
                progToUpdate.merc_marketingmanager = prog.merc_marketingmanager;
                progToUpdate.merc_programmanageremail = prog.merc_programmanageremail;
                progToUpdate.merc_accountemail = prog.merc_accountemail;
                db.SubmitChanges();

                return true;
            }
            catch (Exception ex)
            {
                LogEception(ex, "SaveProgram");
                return false;
            }



            //string SQL = "UPDATE shop_merchantprograms SET merc_name = '" + prog.merc_name + "', merc_programmanageremail = '" + prog.merc_programmanageremail + "', merc_accountemail = '" + prog.merc_accountemail + "', merc_marketingmanager = '" + prog.merc_marketingmanager + "', merc_deniedmessage = '" +  MakeStringSafe(prog.merc_deniedmessage) + "', merc_confirmedmessage = '" + MakeStringSafe(prog.merc_confirmedmessage) + "' WHERE merc_id = " + prog.merc_id;

            //using (SqlConnection cn = new SqlConnection(GetConnectionString()))
            //{
            //    cn.Close();
            //    cn.Open();

            //    SqlCommand cmd = new SqlCommand(SQL, cn);

            //    try
            //    {
            //        cmd.ExecuteNonQuery();
            //    }
            //    catch (Exception ex)
            //    {
            //        LogEception(ex, "SaveProgram");
            //        return false;
            //    }

            //}

            //return true;
        
        }

        [OperationContract]
        public bool SaveLogin(string passwordr, int logid)
        {

            try
            {

                DataClassesDataContext db = new DataClassesDataContext();
                DbUsers toUpdate = db.DbUsers.Single(p => p.log_id == logid);
                toUpdate.log_password = passwordr;
                db.SubmitChanges();


                return true;
            }
            catch (Exception ex)
            {
                LogEception(ex, "SaveLogin");
                return false;
            }


            //string SQL = "UPDATE shop_merchantlogins SET log_password = '" + passwordr + "' WHERE log_id = " + logid;

            //using (SqlConnection cn = new SqlConnection(GetConnectionString()))
            //{
            //    cn.Close();
            //    cn.Open();

            //    SqlCommand cmd = new SqlCommand(SQL, cn);

            //    try
            //    {
            //        cmd.ExecuteNonQuery();
            //    }
            //    catch (Exception ex)
            //    {
            //        LogEception(ex, "SaveLogin");
            //        return false;
            //    }

            //}

            //return true;

        }

        [OperationContract]
        public void ShareTheLove(string name, string email, string friendsName, string friendsEmail, string message)
        {
            string body = friendsName + " would like to let you know about <a href='http://superfox.com.au'>Superfox.com.au</a>, here's what they said.<br/>" + message;

            SendEmail(body, friendsEmail, name + " wants to tell you about Superfox", friendsName);
        }

        [OperationContract]
        public void ContactUs(string name, string phone, string email, string company, string address, string comments)
        {
            string messageBody = "Contact from Superfox.com.au<br/>";
            messageBody += "Name:" + name + "<br/>";
            messageBody += "Phone:" + phone + "<br/>";
            messageBody += "Email:" + email + "<br/>";
            messageBody += "Company:" + company + "<br/>";
            messageBody += "Address:" + address + "<br/>";
            messageBody += "Comment:" + comments + "<br/>";

            SendEmail(messageBody, Config.SUPERFOX_EMAIL_ADDRESS, "Contact from Superfox.com.au", name);
        }


        [OperationContract]
        public List<DbHelp> GetHelpItems(int parentID)
        {
            DataClassesDataContext db = new DataClassesDataContext();

            List<DbHelp> itesm = (from tempObj in db.DbHelps where tempObj.ParentID.Equals(parentID) select tempObj).ToList<DbHelp>();



            return itesm;
        }

        [OperationContract]
        public void DoWork()
        {
            // Add your operation implementation here
            return;
        }

        public void LogEception(Exception e, string function)
        {
            TextWriter tw = new StreamWriter(HttpContext.Current.Server.MapPath("~/" + Config.LOG_FILE_NAME),true);

            tw.Write("<div>[" + DateTime.Now.ToString() + "] " + function + " -> " + e.Message + " : " + e.StackTrace + "</div><br/>\n");

            tw.Close();

        }

        public void SendEmail(string emailBody, string toAddress, string emailSubject, string clientName)
        {
            System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();

            message.From = new System.Net.Mail.MailAddress(Config.FROM_EMAIL,
                Config.FROM_CLIENT_NAME);

            try
            {

                message.To.Add(toAddress);
            }
            catch (Exception ex)
            {
                LogEception(ex, "SendEmail(" + toAddress + ")");
            }

            message.Subject = emailSubject;

            message.IsBodyHtml = true;




            //string htmlContent = File.ReadAllText(Base.Instance.Server.MapPath(Constants.EmailSettings.EMAIL_TEMPLATE_PATH));

            /// now fill in all the feilds in the htmldoc
            //htmlContent = htmlContent.Replace(Constants.EmailSettings.EmailTagsFields.CLIENT_NAME, clientName);
            //htmlContent = htmlContent.Replace(Constants.EmailSettings.EmailTagsFields.MESSAGE_BODY, emailBody);
            //htmlContent = htmlContent.Replace(Constants.EmailSettings.EmailTagsFields.TIME_STAMP, DateTime.Now.ToString());

            System.Net.Mail.AlternateView plainView =
                System.Net.Mail.AlternateView.CreateAlternateViewFromString(emailBody, null,
                Config.PLAIN_MEDIA_TYPE);

            System.Net.Mail.AlternateView htmlView =
                System.Net.Mail.AlternateView.CreateAlternateViewFromString(emailBody, null,
                Config.HTML_MEDIA_TYPE);

            message.AlternateViews.Add(plainView);
            message.AlternateViews.Add(htmlView);

            /// request a read reciept on prelim site inspections
            //if (emailSubject == "Prelim Site Inspection")
            //{

            //    /// add the read receipt information to the email opbject.
            //    message.DeliveryNotificationOptions = System.Net.Mail.DeliveryNotificationOptions.OnSuccess;
            //    message.Headers.Add("Read-Receipt-To", Constants.EmailSettings.EMAIL_FROM_ADDRESS);
            //    message.Headers.Add("Disposition-Notification-To", Constants.EmailSettings.EMAIL_FROM_ADDRESS);

            //}


            System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient();

            // TODO

            client.Port = Config.SMTP_PORT;
            client.Host = Config.SMTP_SERVER;
            
            client.Credentials = new System.Net.NetworkCredential(Config.FROM_EMAIL, Config.EMAIL_PASSWORD);


            try
            {
                
                client.Send(message);
            }
            catch (Exception ex)
            {
                LogEception(ex, "SendEmail");
            }

        }

        [OperationContract]
        public int getInt()
        {
            return 1;
        }

        [OperationContract]
        public string DeclineOrder(int orderID)
        {
            DataClassesDataContext db = new DataClassesDataContext();

            // find the declined message
            


            List<DBOrders> ords = (from tempObj in db.DBOrders where tempObj.ord_id.Equals(orderID) select tempObj).ToList<DBOrders>();

            if (ords == null)
            {
                LogEception(new Exception("Order " + orderID + " counld not be found in the database for authorisation"), "DeclineOrder");
                return "There was an error and this order could not be found. Please contact superfox support.";
            }
            if (ords.Count == 0)
            {
                LogEception(new Exception("Order " + orderID + " counld not be found in the database for authorisation"), "DeclineOrder");
                return "There was an error and this order could not be found. Please contact superfox support.";
            }

            if (ords[0].ord_status == Config.AUTHORISED_ORDER_STATUS)
            {
                LogEception(new Exception("Order " + orderID + " was already authorised"), "DeclineOrder");
                return "This order has already been authorised";
            }

            if (ords[0].ord_status == Config.DECLINED_ORDER_STATUS)
            {
                LogEception(new Exception("Order " + orderID + " was already declined"), "DeclineOrder");
                return "This order has already been declined";
            }

            // find the declined message

            string decMessage = db.DbMarchPrograms.Single(p => p.merc_id == ords[0].merc_id).merc_deniedmessage;

            string SQL = "UPDATE shop_orders SET ord_status = '" + Config.DECLINED_ORDER_STATUS + "' WHERE ord_id = '" + orderID.ToString() + "'";

            using (SqlConnection cn = new SqlConnection(GetConnectionString()))
            {
                cn.Close();
                cn.Open();

                SqlCommand cmd = new SqlCommand(SQL, cn);

                try
                {
                    cmd.ExecuteNonQuery();

                    // notify client order was declined

                    SendEmail(Config.EMAIL_HEADER_STRING.Replace("[message]", decMessage) + ords[0].ord_summary, ords[0].ord_email, "Order " + orderID.ToString() + " has been declined", ords[0].ord_title + " " + ords[0].ord_firstname + " " + ords[0].ord_surname);

                    return "Order " + orderID.ToString() + " has been declined.";
                }
                catch (Exception ex)
                {
                    LogEception(ex, "DeclineOrder");
                    return "There was an error and this order could not be declined. Please contact superfox support.";
                }
            }
        }

        [OperationContract]
        public string AuthoriseOrder(int orderID)
        {

            // check to make sure this order hasn't been authorised alreday

            DataClassesDataContext db = new DataClassesDataContext();

            List<DBOrders> ords = (from tempObj in db.DBOrders where tempObj.ord_id.Equals(orderID) select tempObj).ToList<DBOrders>();

            if (ords == null)
            {
                LogEception(new Exception("Order " + orderID + " counld not be found in the database for authorisation"), "AuthoriseOrder");
                return "There was an error and this order could not be found. Please contact superfox support.";
            }
            if (ords.Count == 0)
            {
                LogEception(new Exception("Order " + orderID + " counld not be found in the database for authorisation"), "AuthoriseOrder");
                return "There was an error and this order could not be found. Please contact superfox support.";
            }

            if (ords[0].ord_status != Config.INITIAL_ORDER_STATUS)
            {
                LogEception(new Exception("Order " + orderID + " was already authorised"), "AuthoriseOrder");
                return "This order has already been authorised";
            }

            // find the accepted message

            string accMEssage = db.DbMarchPrograms.Single(p => p.merc_id == ords[0].merc_id).merc_confirmedmessage;

            string SQL = "UPDATE shop_orders SET ord_status = '" + Config.AUTHORISED_ORDER_STATUS + "' WHERE ord_id = '" + orderID.ToString() + "'";
            

            using (SqlConnection cn = new SqlConnection(GetConnectionString()))
            {
                cn.Close();
                cn.Open();

                SqlCommand cmd = new SqlCommand(SQL, cn);

                try
                {
                    cmd.ExecuteNonQuery();

                    // notify that order has been authorised

                    // to person who ordered it
                    SendEmail(Config.EMAIL_HEADER_STRING.Replace("[message]", accMEssage) + ords[0].ord_summary, ords[0].ord_email, "Order " + orderID.ToString() + " has been approved", ords[0].ord_title + " " + ords[0].ord_firstname + " " + ords[0].ord_surname);


                    // to superfox
                    SendEmail(Config.EMAIL_HEADER_STRING.Replace("[message]", "Order ready to be processed") + ords[0].ord_summary, Config.SUPERFOX_EMAIL_ADDRESS, "Order " + orderID.ToString() + " has been approved", "Superfox");

                    // get merc details

                    List<DbMarchPrograms> progs = (from tempObj in db.DbMarchPrograms where tempObj.merc_id.Equals(ords[0].merc_id) select tempObj).ToList<DbMarchPrograms>();

                    if(progs == null)
                    {
                        LogEception(new Exception("Order " + ords[0].ord_id.ToString() + " no merch program count be found"),"AuthoriseOrder");
                        return "There was a database error looking up the accounts email address";
                    }

                    if(progs.Count == 0)
                    {
                        LogEception(new Exception("Order " + ords[0].ord_id.ToString() + " no merch program count be found"),"AuthoriseOrder");
                        return "There was a database error looking up the accounts email address";
                    }

                    // email too marketing manager

                    SendEmail(Config.EMAIL_HEADER_STRING.Replace("[message]", "Order Complete") + ords[0].ord_summary, progs[0].merc_marketingmanager, "Order " + orderID.ToString() + " for Superfox", "Marketing Manager");  

                    // email to accounts

                    SendEmail(Config.EMAIL_HEADER_STRING.Replace("[message]", "Order ready to be paied") + ords[0].ord_summary, progs[0].merc_accountemail, "Order " + orderID.ToString() + " for Superfox", "Account Manager");  

                    return "Order " + orderID.ToString() + " has been authorised.";
                }
                catch (Exception ex)
                {
                    LogEception(ex, "AuthoriseOrder");
                    return "There was an error and this order could not be authorised. Please contact superfox support.";
                }
            }

            

        }

        [OperationContract]
        public List<int> GetAllOrderIDs(int merch_id)
        {
            DataClassesDataContext db = new DataClassesDataContext();

            List<int> ids = (from tempObj in db.DBOrders where tempObj.merc_id.Equals(merch_id) select tempObj.ord_id).ToList<int>();

            return ids;

        }

        [OperationContract]
        public DBOrders GetOrderById(int ord_id)
        {
            DataClassesDataContext db = new DataClassesDataContext();

            DBOrders ord = (from tempObj in db.DBOrders where tempObj.ord_id.Equals(ord_id) select tempObj).Single<DBOrders>();


            ord.Products = ord.DbOrderProducts.ToList<DbOrderProduct>();

            return ord;
        }



        public string BuildDataTable(ShoppingCartCollection sc)
        {
            string HTML = "<table style=\"font-family:Verdana, Geneva, sans-serif; font-size:10px;\" cellpadding=\"7\" cellspacing=\"0\" bordercolorlight=\"#999999\" border=\"1\" bordercolordark=\"#999999\" bordercolor=\"#999999\"><tr><td style=\"background-color:#CCC;\"><b>Qty</b></td><td style=\"background-color:#CCC;\"><b>Item</b></td><td style=\"background-color:#CCC;\"><b>Subtotal</b></td></tr>";



            decimal cost = 0;

            foreach (DbProducts p in sc)
            {
                string description = p.prod_name;
                

                int totalAmount = 0;

                foreach (DbProductOptionsValues v in p.Options.Values)
                {
                    if (v.TotalSelected < 1)
                        continue;

                    totalAmount += v.TotalSelected;
                    description += " <br/>" + v.pov_name + " X " + v.TotalSelected;
                }

                cost += (p.prod_price * totalAmount);

                string row = "<tr><td>"+totalAmount.ToString()+"</td><td>"+description+"</td><td>$"+Decimal.Round(p.prod_price * totalAmount,2).ToString()+"</td></tr>";
                
                HTML += row;
            }

            string row2 = "<tr><td ></td><td align=\"right\">GST Included in Total</td><td >$"+Decimal.Round(cost * (decimal)0.1,2).ToString()+"</td></tr>";

            string row3 = "<tr><td ></td><td align=\"right\"><b>Total</b></td><td><b>$"+Decimal.Round(cost * (decimal)1.1,2).ToString()+"</b></td></tr></table>";

            HTML += row2 + row3;

            return HTML;
        }

        public string BuildHtml(Order o, ShoppingCartCollection sc, Config.EMAIL_TYPES htmlType)
        {
            string HTML = "";

            try
            {

                HTML = File.ReadAllText(HttpContext.Current.Server.MapPath("~/" + Config.EMAIL_TEMAPLTE_NAME));

            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            string person = "Name: " + o.PersonalInfo.PersonInfo.Title + " " + o.PersonalInfo.PersonInfo.FirstName + " " + o.PersonalInfo.PersonInfo.Surname + "<br/>";
            person += "Email: " + o.PersonalInfo.Email + "<br/>";
            person += "Company: " + o.PersonalInfo.Company + "<br/>";
            person += "Telephone: " + o.PersonalInfo.Telephone + "<br/>";
            person += "Campaign Title: " + o.PersonalInfo.CampaginTitle + "<br/>";
            person += "Comments: " + o.Comments;

            string shipping = o.ShippingInfo.PersonInfo.Title + " " + o.ShippingInfo.PersonInfo.FirstName + " " + o.ShippingInfo.PersonInfo.Surname + " <br/>";
            shipping += o.ShippingInfo.AddressInfo.StreetAddress + "<br/>";
            shipping += o.ShippingInfo.AddressInfo.Suburb + "<br/>";
            shipping += o.ShippingInfo.AddressInfo.State + " " + o.ShippingInfo.AddressInfo.Postcode + ", " + o.ShippingInfo.AddressInfo.Country;
            shipping += o.ShippingInfo.BranchName;

            // insert the values
            HTML = HTML.Replace("[person]", person);
            HTML = HTML.Replace("[shipping]", shipping);
            HTML = HTML.Replace("[data]", BuildDataTable(sc));
            HTML = HTML.Replace("[date]", DateTime.Now.ToString());

            switch (htmlType)
            {
                case Config.EMAIL_TYPES.ClientPrelim:
                    HTML = HTML.Replace("[message]", o.PersonalInfo.PersonInfo.FirstName + Config.CLIENT_EMAIL_MESSAGE);
                    break;

                case Config.EMAIL_TYPES.LineManagerRequest:

                    HTML = HTML.Replace("[message]", "<b>Please click <a href='http://superfox.com.au/SuperFoxTestPage.aspx?action=au&orderid=" + o.OrderNumber.ToString() + "'>here<a/> to authorise this order or click <a href='http://superfox.com.au/SuperFoxTestPage.aspx?action=de&orderid=" + o.OrderNumber.ToString() + "'>here<a/> to decline it</b>");
                    break;

                case Config.EMAIL_TYPES.Confirm:

                    break;

            }

            

            HTML = HTML.Replace("[order_number]", o.OrderNumber.ToString());

            return HTML;
        }

        public void InsertHTML(string HTML, int on)
        {

            string SQL = "UPDATE shop_orders SET ord_summary = '" + MakeStringSafe(HTML) + "' WHERE ord_id = '" + on.ToString() + "'";

            using (SqlConnection cn = new SqlConnection(GetConnectionString()))
            {
                cn.Close();
                cn.Open();

                SqlCommand cmd = new SqlCommand(SQL, cn);

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    LogEception(ex, "BuildHtml");
                }

                cn.Close();
            }


        }

        public void InsertProducts(ShoppingCartCollection sc, int iid)
        {
            using (SqlConnection cn = new SqlConnection(GetConnectionString()))
            {
                cn.Close();
                cn.Open();

                

                foreach (DbProducts p in sc)
                {
                    
                    string SQL = "INSERT INTO shop_orderproducts (ord_id, prod_id, ordprod_quantity,ordprod_productname,ordprod_totalproductcost,ordprod_sku,ordprod_selectedoptions) VALUES ('" + iid.ToString() + "',";

                    SQL += "'" + p.prod_id + "',";

                    int subTotal = 0;
                    string subString = "";
                    foreach (DbProductOptionsValues v in p.Options.Values)
                    {

                        if (v.TotalSelected < 1)
                            continue;

                        subString += v.TotalSelected.ToString() + " X " + v.pov_name + ", ";

                        subTotal += v.TotalSelected;
                    }

                    SQL += "'" + subTotal.ToString() + "','" + p.prod_name + "','" + (subTotal * p.prod_price).ToString() + "','','" + subString + "')";

                    SqlCommand cmd = new SqlCommand(SQL, cn);

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        LogEception(ex, "InsertProducts");
                    }
                }
            }
        }

        [OperationContract]
        public int PlaceOrder(DbUsers u, ShoppingCartCollection sc)
        {


            /* TODO: add IP address in */

            string SQL = "INSERT INTO shop_orders (merc_id, ord_date, ord_status, ord_notes, ord_customercomments, ord_summary, ord_totalOrderCost, ord_ip, ord_totalProductCost, ord_totalPostageCost, ord_deleted, ord_email, ord_title, ord_surname, ord_firstname, ord_telephone01, ord_telephone02, ord_address, ord_suburb, ord_state, ord_postcode, ord_country, ord_purchaseordernumber, ord_company, ord_branchname, ord_shippingtitle, ord_shippingfirstname, ord_shippingsurname, ord_costcenter) " +
                "VALUES ('" + u.UserOrderInfo.MarchantId.ToString() + "','" + DateTime.Today.Month.ToString() + "/" + DateTime.Today.Day.ToString() + "/" + DateTime.Today.Year.ToString() + "','" + Config.INITIAL_ORDER_STATUS + "','','" + MakeStringSafe(u.UserOrderInfo.Comments) + "','HTML','" + u.UserOrderInfo.TotalCost.ToString() + "','" + u.UserOrderInfo.OrderIP + "','" + u.UserOrderInfo.TotalProductCost.ToString() + "','" + u.UserOrderInfo.TotalPostageCost.ToString() + "','0','" + MakeStringSafe(u.UserOrderInfo.PersonalInfo.Email) + "','" + MakeStringSafe(u.UserOrderInfo.PersonalInfo.PersonInfo.Title) + "','" + MakeStringSafe(u.UserOrderInfo.PersonalInfo.PersonInfo.FirstName) + "','" + MakeStringSafe(u.UserOrderInfo.PersonalInfo.PersonInfo.Surname) + "','" + MakeStringSafe(u.UserOrderInfo.PersonalInfo.Telephone) + "','" + MakeStringSafe(u.UserOrderInfo.PersonalInfo.Mobile) + "','" + MakeStringSafe(u.UserOrderInfo.ShippingInfo.AddressInfo.StreetAddress) + "','" + MakeStringSafe(u.UserOrderInfo.ShippingInfo.AddressInfo.Suburb) + "','" + MakeStringSafe(u.UserOrderInfo.ShippingInfo.AddressInfo.State) + "','" + MakeStringSafe(u.UserOrderInfo.ShippingInfo.AddressInfo.Postcode) + "','" + MakeStringSafe(u.UserOrderInfo.ShippingInfo.AddressInfo.Country) + "','" + MakeStringSafe(u.UserOrderInfo.PersonalInfo.CampaginTitle) + "','" + MakeStringSafe(u.UserOrderInfo.PersonalInfo.Company) + "','" + MakeStringSafe(u.UserOrderInfo.ShippingInfo.BranchName) + "','" + MakeStringSafe(u.UserOrderInfo.ShippingInfo.PersonInfo.Title) + "','" + MakeStringSafe(u.UserOrderInfo.ShippingInfo.PersonInfo.FirstName) + "','" + MakeStringSafe(u.UserOrderInfo.ShippingInfo.PersonInfo.Surname) + "','" + MakeStringSafe(u.UserOrderInfo.ShippingInfo.CostCenter) + "'); SELECT @@IDENTITY";
            

            int newID = 0;

            using (SqlConnection cn = new SqlConnection(GetConnectionString()))
            {

                cn.Close();
                cn.Open();

                SqlCommand cmd = new SqlCommand(SQL, cn);

                SqlDataReader reader = null;

                try
                {
                    reader = cmd.ExecuteReader();
                }
                catch (Exception ex)
                {
                    LogEception(ex, "PlaceOrder");

                    return -1;
                }

                if (reader.Read())
                {
                    try
                    {

                        newID = Int32.Parse(reader.GetDecimal(0).ToString());
                    }
                    catch (Exception ex)
                    {

                        LogEception(ex, "PlaceOrder");

                        return -1;
                    }
                }
                else
                {
                    return -1;
                }

                cn.Close();

                // now add in the html

                u.UserOrderInfo.OrderNumber = newID;

                string html = BuildHtml(u.UserOrderInfo, sc, Config.EMAIL_TYPES.ClientPrelim);

                InsertHTML(html, newID);
                InsertProducts(sc, newID);

                DecrementStock(sc);

                // send email to order placer
                SendEmail(html, u.UserOrderInfo.PersonalInfo.Email, "Superfox Order (id = " + newID.ToString() + ")", u.UserOrderInfo.PersonalInfo.PersonInfo.Title + " " + u.UserOrderInfo.PersonalInfo.PersonInfo.FirstName + " " + u.UserOrderInfo.PersonalInfo.PersonInfo.Surname);

                // send email to a line manager
                SendEmail(BuildHtml(u.UserOrderInfo, sc, Config.EMAIL_TYPES.LineManagerRequest), u.Programs.merc_programmanageremail, "Superfox Order (id = " + newID.ToString() + ")", u.Programs.merc_name);

                return newID;
            }
        }

        private List<int> Deserialize(string xml)
        {

            if (xml == null || xml == string.Empty || xml == "")
            {
                return null;
            }

            try
            {

                XmlSerializer serializer = new XmlSerializer(typeof(List<int>));
                return (List<int>)serializer.Deserialize(new StringReader(xml));

            }
            catch (Exception ex)
            {
                LogEception(ex, "Deserilize");
                return null;
            }
            

        }

        [OperationContract]
        public DbUsers login(string un, string pw)
        {

            DataClassesDataContext db = new DataClassesDataContext();

            List<DbUsers> users = (from tempObj in db.DbUsers
                                   select tempObj).ToList<DbUsers>();

            if (users == null)
                return null;

            if (users.Count > 0)
            {
                foreach (DbUsers u in users)
                {
                    if (u.log_username.Trim().ToLower() == un)
                    {
                        if (u.log_password.Trim().ToLower() == pw)
                        {

                            // filter which products can be seen by each user
                            u.DbMarchPrograms.Products = u.DbMarchPrograms.DbProducts.ToList<DbProducts>();
                            
                            //u.DbMarchPrograms.Products = new List<DbProducts>();

                            foreach (DbProducts pr in u.DbMarchPrograms.Products)
                            {

                                List<int> validIDs = Deserialize(pr.prod_users);

                                // if something goes wrong just add the product
                                if (validIDs == null)
                                {
                                    pr.Visible = true;
                                    continue;
                                }

                                if (validIDs.Contains(u.log_id))
                                {
                                    pr.Visible = true;
                                }
                                else
                                {
                                    pr.Visible = false;
                                }


                            }
                            
                            
                            u.DbMarchPrograms.Brnaches = u.DbMarchPrograms.DbBranches.ToList<DbBranches>();
                            u.DbMarchPrograms.CostCenters = u.DbMarchPrograms.DbCostCenters.ToList<DbCostCenters>();
                            u.DbMarchPrograms.Style = u.DbMarchPrograms.DbStyles;
                            u.DbMarchPrograms.Reqfields = u.DbMarchPrograms.DbMarchantRequiredFields;

                            foreach (DbProducts pro in u.DbMarchPrograms.Products)
                            {
                                pro.Options = pro.DbProductOptions;

                                pro.PriceTiers = pro.DbPriceTiers.ToList<DbPriceTiers>();

                                pro.Options.Values = pro.DbProductOptions.DbProductOptionsValues.ToList<DbProductOptionsValues>();

                                foreach (DbProductOptionsValues pov in pro.Options.Values)
                                {
                                    pov.Price = pro.prod_price;
                                }

                                //foreach (DbProductOptionsValues val in pro.Options.DbProductOptionsValues)
                                //{
                                //    pro.Options.Values.Add(val);
                                //}
                            }

                            u.Programs = u.DbMarchPrograms;

                            return u;

                        }
                    }
                    else
                        continue;
                }
            }

            return null;
        }

        public string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["superfConnectionString"].ConnectionString;
        }

        public string MakeStringSafe(string str)
        {
            if (str == null)
                return null;

            return str.Replace("'", "''");
        }

        // Add more operations here and mark them with [OperationContract]
    }
}
